package db;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import model.Address;

public class AddressDAO {
	private static final String TABLE = "address";	
	private static String onSqlError = "SQL statement fault";
	
	private static final String 
		ID_FIELD = "address",	
		ADDRESS_TYPE_FIELD = "address_type_fk",
		SUBJECT_FIELD = "subject_fk",
		SUBJECT_TYPE_FIELD = "subject_type_fk",		
		COUNTRY_FIELD = "country",
		COUNTY_FIELD = "county",
		TOWN_VILLAGE_FIELD = "town_village",
		STREET_ADDRESS_FIELD = "street_address",
		ZIPCODE_FIELD = "zipcode";
	
	private static final String INSERT_STATEMENT_STRING = 
			"INSERT INTO "+TABLE+" " +
			"("+ADDRESS_TYPE_FIELD+", "+SUBJECT_FIELD+", "+SUBJECT_TYPE_FIELD+", " +COUNTRY_FIELD+", "
			+COUNTY_FIELD+", "+TOWN_VILLAGE_FIELD+", "+STREET_ADDRESS_FIELD+", "+ZIPCODE_FIELD+") " +
			"VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
	
	private static final String UPDATE_STATEMENT_STRING = 
			"UPDATE "+TABLE+" SET " 
			+ADDRESS_TYPE_FIELD+" = ?, "+SUBJECT_FIELD+" = ?, "+SUBJECT_TYPE_FIELD+" = ?, "+COUNTRY_FIELD+" = ?, "
			+COUNTY_FIELD+" = ?, "+TOWN_VILLAGE_FIELD+" = ?, "+STREET_ADDRESS_FIELD+" = ?, "+ZIPCODE_FIELD+" = ? " +
			"WHERE "+ID_FIELD+" = ?";
	
	private static final String DELETE_STATEMENT_STRING =
			"DELETE FROM "+TABLE+" WHERE "+ID_FIELD+" = ?";
	
	private static PreparedStatement insertStatement, updateStatement, deleteStatement;
	
	static{
		try {
			insertStatement = ImbiConnection.getConnection().prepareStatement(INSERT_STATEMENT_STRING);
			updateStatement = ImbiConnection.getConnection().prepareStatement(UPDATE_STATEMENT_STRING);
			deleteStatement = ImbiConnection.getConnection().prepareStatement(DELETE_STATEMENT_STRING);
		} catch (SQLException e) {			
			e.printStackTrace();
			System.exit(0);
		}
	}
	
	
	public Address find(int id) {			
		try{				
			Statement st = ImbiConnection.getConnection().createStatement();
			st.executeQuery("SELECT * FROM "+TABLE+" WHERE "+ID_FIELD+" = "+id);						
			Address address = extractAddress(st.getResultSet()).get(0);						
			return address;
		} catch (SQLException | IndexOutOfBoundsException e){
				System.out.println(onSqlError);				
				return null;
		}		
	}

	private ArrayList<Address> extractAddress(ResultSet r) throws SQLException {
		ArrayList<Address> list = new ArrayList<Address>();
		while(r.next()){
			Address address = new Address(
			r.getInt(1),
			r.getInt(2),
			r.getInt(3),
			r.getInt(4),
			r.getString(5),
			r.getString(6),
			r.getString(7),
			r.getString(8),
			r.getString(9)					
			);
			list.add(address);
		}
		return list;
	}	
	
	public ArrayList<Address> findBySubjectId(int subjectId, int subjectType) {			
		try{				
			Statement st = ImbiConnection.getConnection().createStatement();
			st.executeQuery("SELECT * FROM "+TABLE+" WHERE "+SUBJECT_FIELD+" = "+subjectId+" AND "+SUBJECT_TYPE_FIELD+" = "+subjectType);						
			return extractAddress(st.getResultSet());			
		} catch (SQLException s){
				System.out.println(onSqlError);				
				return null;
		}		
	}
	
	/**
	 * Inserts new row and generates new ID for it.
	 * @param a Address data, the id isn't used by this method, as the database handles it automatically	 
	 */
	public void insert(Address a){		
		try{											
		    insertStatement.setInt(1, a.getAddressType());
		    insertStatement.setInt(2, a.getSubject());
		    insertStatement.setInt(3, a.getSubjectType());
		    insertStatement.setString(4, a.getCountry());
		    insertStatement.setString(5, a.getCounty());
		    insertStatement.setString(6, a.getTownVillage());
		    insertStatement.setString(7, a.getStreetAddress());
		    insertStatement.setString(8, a.getZipcode());
		    insertStatement.executeUpdate();			
		} catch (SQLException s){
				System.out.println(onSqlError);								
		}			
	}
	
	public void update(Address a){
		try{										
		    updateStatement.setInt(1, a.getAddressType());
		    updateStatement.setInt(2, a.getSubject());
		    updateStatement.setInt(3, a.getSubjectType());
		    updateStatement.setString(4, a.getCountry());
		    updateStatement.setString(5, a.getCounty());
		    updateStatement.setString(6, a.getTownVillage());
		    updateStatement.setString(7, a.getStreetAddress());
		    updateStatement.setString(8, a.getZipcode());
		    updateStatement.setInt(9, a.getId());
		    updateStatement.executeUpdate();			
		} catch (SQLException s){
				System.out.println(onSqlError);									
		}			
	}
	
	public void delete(Address a){
		try{										
		    deleteStatement.setInt(1, a.getId());		    
		    deleteStatement.executeUpdate();			
		} catch (SQLException s){
				System.out.println(onSqlError);					
		}			
	}
	
	public int getNextId(){
		try{				
			Statement st = ImbiConnection.getConnection().createStatement();
			st.executeQuery("SELECT "+ID_FIELD+" FROM "+TABLE+" ORDER BY "+ID_FIELD+" DESC LIMIT 1");
			ResultSet r = st.getResultSet();			
			r.next();		
			return r.getInt(1) +1;
		} catch (SQLException s){
				System.out.println(onSqlError);				
				return 0;
		}		
	}		
}
